﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Configuration;
using MySql.Data.MySqlClient;
using MySql.Data;
using OnlineCooperation.Entity;

namespace OnlineCooperation.DB
{
    public class RelUserTeamDB
    {
        String strConn;
        datasetMgn DSMgn;

        //构造函数， 为strConn赋值
        public RelUserTeamDB()
        {
            strConn = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
            DSMgn = new datasetMgn();
        }

        /// <summary>
        /// 返回当前用户所在所有团队
        /// </summary>
        /// <param name="user">用户</param>
        /// <returns>查询结果：返回当前用户所在所有团队的列表</returns>
        public List<Team> GetAllTeam(User user)
        {
            String strSql = "SELECT * FROM `reluserteam` WHERE `UserID`='" + user.ID + "'";
            DataSet ds = MySqlHelper.ExecuteDataset(strConn, strSql);
            List<Team> AllTeam = new List<Team>();
            TeamDB TDB = new TeamDB(); 
            for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
            {
                int TID = Convert.ToInt32(ds.Tables[0].Rows[i][2].ToString());
                Team TempTeam = TDB.GetTeamByID(TID);
                if (TempTeam.ID != -1)
                    AllTeam.Add(TempTeam);
            }

            return AllTeam;
        }

        /// <summary>
        /// 返回当前团队所有成员
        /// </summary>
        /// <param name="team">团队</param>
        /// <returns>查询结果：返回当前团队所有成员的列表</returns>
        public List<User> GetAllMember(Team team)
        {
            String strSql = "SELECT * FROM `reluserteam` WHERE `TeamID`='" + team.ID + "'";
            DataSet ds = MySqlHelper.ExecuteDataset(strConn, strSql);
            List<User> AllMember = new List<User>();
            UserDB UDB = new UserDB();
            for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
            {
                int UID = Convert.ToInt32(ds.Tables[0].Rows[i][1].ToString());
                User TempUser = UDB.GetUserByID(UID);
                if (TempUser.ID != -1)
                    AllMember.Add(TempUser);
            }

            return AllMember;
        }

        /// <summary>
        /// 把记录添加到用户团队关系表中
        /// </summary>
        /// <param name="team">团队</param>
        /// <param name="user">用户</param>
        /// <returns>添加的结果：若该team或user不合法返回-1，或者该用户原本就在该团队，返回0失败；否则返回1成功</returns>
        public int addRel(Team team, User user)
        {
            //判断该team或user是否合法
            String strSql = "SELECT * FROM `teaminfo` WHERE `TeamID`='" + team.ID + "'";
            DataSet ds = DSMgn.ReadDataSet(strSql, strConn);
            if (ds.Tables[0].Rows.Count == 0)
                return -1;                                                                                  //不存在该团队，添加关系失败
            strSql = "SELECT * FROM `userinfo` WHERE `UserID`='" + user.ID + "'";
            ds = DSMgn.ReadDataSet(strSql, strConn);
            if (ds.Tables[0].Rows.Count == 0)
                return -1;                                                                                  //不存在该用户，添加关系失败

            //判断该用户是否原本就在该团队
            strSql = "SELECT * FROM `reluserteam` WHERE `UserID`='" + user.ID + "' AND `TeamID`='" + team.ID + "'";
            ds = DSMgn.ReadDataSet(strSql, strConn);
            if (ds.Tables[0].Rows.Count != 0)
                return 0;                                                                                   //该用户原本就在该团队，添加关系失败

            strSql = "INSERT INTO `reluserteam`(`UserID`,`TeamID`) VALUES(?UserID0, ?TeamID0)";
            MySqlParameter[] parameters = new MySqlParameter[2];
            parameters[0] = new MySqlParameter("?UserID0", MySqlDbType.Int32);
            parameters[0].Value = user.ID;
            parameters[1] = new MySqlParameter("?TeamID0", MySqlDbType.Int32);
            parameters[1].Value = team.ID;
            MySqlHelper.ExecuteNonQuery(strConn, strSql, parameters);

            //在团队内发布新鲜事，表明该用户加入了该团队
            strSql = "SELECT max(`PostID`) FROM `post`";
            String CurrentMaxPostID = DSMgn.Readstr(strSql, 0, strConn);
            if (CurrentMaxPostID == "")
                CurrentMaxPostID = "-1";
            strSql = "INSERT INTO `post`(`PostID`,`StateID`,`PostContent`,`TopicID`,`PostTeamID`,`PDateAndTime`) VALUES(?PostID2, ?StateID2, ?PostContent2, ?TopicID2, ?PostTeamID2, ?PDateAndTime2)";
            MySqlParameter[] parameters2 = new MySqlParameter[6];
            parameters2[0] = new MySqlParameter("?PostID2", MySqlDbType.Int32);
            parameters2[0].Value = Convert.ToInt32(CurrentMaxPostID) + 1;
            parameters2[1] = new MySqlParameter("?StateID2", MySqlDbType.Int32);
            parameters2[1].Value = 0;                                                            //所有新鲜事的StateID为0
            parameters2[2] = new MySqlParameter("?PostContent2", MySqlDbType.VarChar);
            parameters2[2].Value = "用户" + user.Name + "加入了本团队，欢迎^_^";
            parameters2[3] = new MySqlParameter("?TopicID2", MySqlDbType.Int32);
            parameters2[3].Value = 0;
            parameters2[4] = new MySqlParameter("?PostTeamID2", MySqlDbType.Int32);
            parameters2[4].Value = team.ID;
            parameters2[5] = new MySqlParameter("?PDateAndTime2", MySqlDbType.DateTime);
            parameters2[5].Value = DateTime.Now;
            MySqlHelper.ExecuteNonQuery(strConn, strSql, parameters2);

            return 1;                                                                                        //添加关系成功
        }

        /// <summary>
        /// 把记录添加到用户团队关系表中
        /// </summary>
        /// <param name="teamid">团队编号</param>
        /// <param name="userid">用户编号</param>
        /// <returns>添加的结果：若该团队或用户不合法返回-1，或者该用户原本就在该团队，返回0失败；否则返回1成功</returns>
        public int addRel(int teamid, int userid)
        {
            //判断该team或user是否合法
            String strSql = "SELECT * FROM `teaminfo` WHERE `TeamID`='" + teamid + "'";
            DataSet ds = DSMgn.ReadDataSet(strSql, strConn);
            if (ds.Tables[0].Rows.Count == 0)
                return -1;                                                                                  //不存在该团队，添加关系失败
            strSql = "SELECT * FROM `userinfo` WHERE `UserID`='" + userid + "'";
            ds = DSMgn.ReadDataSet(strSql, strConn);
            if (ds.Tables[0].Rows.Count == 0)
                return -1;                                                                                  //不存在该用户，添加关系失败

            //判断该用户是否原本就在该团队
            strSql = "SELECT * FROM `reluserteam` WHERE `UserID`='" + userid + "' AND `TeamID`='" + teamid + "'";
            ds = DSMgn.ReadDataSet(strSql, strConn);
            if (ds.Tables[0].Rows.Count != 0)
                return 0;                                                                                   //该用户原本就在该团队，添加关系失败

            strSql = "INSERT INTO `reluserteam`(`UserID`,`TeamID`) VALUES(?UserID0, ?TeamID0)";
            MySqlParameter[] parameters = new MySqlParameter[2];
            parameters[0] = new MySqlParameter("?UserID0", MySqlDbType.Int32);
            parameters[0].Value = userid;
            parameters[1] = new MySqlParameter("?TeamID0", MySqlDbType.Int32);
            parameters[1].Value = teamid;
            MySqlHelper.ExecuteNonQuery(strConn, strSql, parameters);

            //在团队内发布新鲜事，表明该用户加入了该团队
            strSql = "SELECT max(`PostID`) FROM `post`";
            String CurrentMaxPostID = DSMgn.Readstr(strSql, 0, strConn);
            if (CurrentMaxPostID == "")
                CurrentMaxPostID = "-1";
            UserDB UDB = new UserDB();
            strSql = "INSERT INTO `post`(`PostID`,`StateID`,`PostContent`,`TopicID`,`PostTeamID`,`PDateAndTime`) VALUES(?PostID2, ?StateID2, ?PostContent2, ?TopicID2, ?PostTeamID2, ?PDateAndTime2)";
            MySqlParameter[] parameters2 = new MySqlParameter[6];
            parameters2[0] = new MySqlParameter("?PostID2", MySqlDbType.Int32);
            parameters2[0].Value = Convert.ToInt32(CurrentMaxPostID) + 1;
            parameters2[1] = new MySqlParameter("?StateID2", MySqlDbType.Int32);
            parameters2[1].Value = 0;                                                            //所有新鲜事的StateID为0
            parameters2[2] = new MySqlParameter("?PostContent2", MySqlDbType.VarChar);
            parameters2[2].Value = "用户" + UDB.GetUserByID(userid).Name + "加入了本团队，欢迎^_^";
            parameters2[3] = new MySqlParameter("?TopicID2", MySqlDbType.Int32);
            parameters2[3].Value = 0;
            parameters2[4] = new MySqlParameter("?PostTeamID2", MySqlDbType.Int32);
            parameters2[4].Value = teamid;
            parameters2[5] = new MySqlParameter("?PDateAndTime2", MySqlDbType.DateTime);
            parameters2[5].Value = DateTime.Now;
            MySqlHelper.ExecuteNonQuery(strConn, strSql, parameters2);

            return 1;                                                                                        //添加关系成功
        }

        /// <summary>
        /// 把记录从用户团队关系列表中移除
        /// </summary>
        /// <param name="team">团队</param>
        /// <param name="user">用户</param>
        /// <returns>删除的结果：若该team或user不合法返回-1，或者该用户原本就不在该团队，返回0失败；否则返回1成功</returns>
        public int remRel(Team team, User user)
        {
            //判断该team或user是否合法
            String strSql = "SELECT * FROM `teaminfo` WHERE `TeamID`='" + team.ID + "'";
            DataSet ds = DSMgn.ReadDataSet(strSql, strConn);
            if (ds.Tables[0].Rows.Count == 0)
                return -1;                                                                                  //不存在该团队，添加关系失败
            strSql = "SELECT * FROM `userinfo` WHERE `UserID`='" + user.ID + "'";
            ds = DSMgn.ReadDataSet(strSql, strConn);
            if (ds.Tables[0].Rows.Count == 0)
                return -1;                                                                                  //不存在该用户，删除关系失败

            //判断该用户是否原本就不在该团队
            strSql = "SELECT * FROM `reluserteam` WHERE `UserID`='" + user.ID + "' AND `TeamID`='" + team.ID + "'";
            ds = DSMgn.ReadDataSet(strSql, strConn);
            if (ds.Tables[0].Rows.Count == 0)
                return 0;                                                                                   //该用户原本就不在该团队，删除关系失败

            strSql = "DELETE  FROM `reluserteam` WHERE `UserID`='" + user.ID + "' AND `TeamID`='" + team.ID + "'"; 
            MySqlHelper.ExecuteNonQuery(strConn, strSql, null);

            //在团队内发布新鲜事，表明该用户退出了该团队
            strSql = "SELECT max(`PostID`) FROM `post`";
            String CurrentMaxPostID = DSMgn.Readstr(strSql, 0, strConn);
            if (CurrentMaxPostID == "")
                CurrentMaxPostID = "-1";
            strSql = "INSERT INTO `post`(`PostID`,`StateID`,`PostContent`,`TopicID`,`PostTeamID`,`PDateAndTime`) VALUES(?PostID2, ?StateID2, ?PostContent2, ?TopicID2, ?PostTeamID2, ?PDateAndTime2)";
            MySqlParameter[] parameters2 = new MySqlParameter[6];
            parameters2[0] = new MySqlParameter("?PostID2", MySqlDbType.Int32);
            parameters2[0].Value = Convert.ToInt32(CurrentMaxPostID) + 1;
            parameters2[1] = new MySqlParameter("?StateID2", MySqlDbType.Int32);
            parameters2[1].Value = 0;                                                            //所有新鲜事的StateID为0
            parameters2[2] = new MySqlParameter("?PostContent2", MySqlDbType.VarChar);
            parameters2[2].Value = "用户" + user.Name + "已经退出本团队~";
            parameters2[3] = new MySqlParameter("?TopicID2", MySqlDbType.Int32);
            parameters2[3].Value = 0;
            parameters2[4] = new MySqlParameter("?PostTeamID2", MySqlDbType.Int32);
            parameters2[4].Value = team.ID;
            parameters2[5] = new MySqlParameter("?PDateAndTime2", MySqlDbType.DateTime);
            parameters2[5].Value = DateTime.Now;
            MySqlHelper.ExecuteNonQuery(strConn, strSql, parameters2);

            return 1;                                                                                       //删除关系成功
        }
    }
}